-- 部门表/支部表 增加is_del
alter table inspection_branch
	add is_del int default 0 not null
go

alter table inspection_department
	add is_del int default 0 not null
go

alter table inspection_department
	add parent_id bigint
go

alter table inspection_department
	add is_leaf_node int default 0
go


update dbo.inspection_department set parent_id=-1,is_leaf_node =1 





DROP TABLE [dbo].[sys_dept]






DROP TABLE [dbo].[sys_user]

CREATE TABLE [dbo].[sys_user] (
  [id] bigint  IDENTITY(1,1) NOT NULL,
  [user_code] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [user_name] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [mobile] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [password] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [real_name] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [work_phone] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [email] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [salt] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [user_status] int  NULL,
  [dept_id] bigint  NULL,
  [date_create] datetime  NULL,
  [date_update] datetime  NULL,
  [is_del] int  NULL,
  CONSTRAINT [PK__sys_user__B9BE370F0CDAE408] PRIMARY KEY CLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY],
  CONSTRAINT [uk_usercode] UNIQUE NONCLUSTERED ([user_code] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY],
  CONSTRAINT [uk_username] UNIQUE NONCLUSTERED ([user_name] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[sys_user] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'用户编号',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'user_code'
GO

EXEC sp_addextendedproperty
'MS_Description', N'用户名',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'user_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'手机号登錄賬號',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'mobile'
GO

EXEC sp_addextendedproperty
'MS_Description', N'密码',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'password'
GO

EXEC sp_addextendedproperty
'MS_Description', N'真名',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'real_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'工作机号',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'work_phone'
GO

EXEC sp_addextendedproperty
'MS_Description', N'邮箱',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'email'
GO

EXEC sp_addextendedproperty
'MS_Description', N'盐',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'salt'
GO

EXEC sp_addextendedproperty
'MS_Description', N'状态  -1：禁用   1：正常',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'user_status'
GO

EXEC sp_addextendedproperty
'MS_Description', N'部门ID',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'dept_id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'date_create'
GO

EXEC sp_addextendedproperty
'MS_Description', N'0 未删除 1 已删除',
'SCHEMA', N'dbo',
'TABLE', N'sys_user',
'COLUMN', N'is_del'





CREATE TABLE [dbo].[sys_config] (
  [id] bigint  IDENTITY(1,1) NOT NULL,
  [config_code] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [config_name] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [config_value] varchar(1024) COLLATE Chinese_PRC_CI_AS  NULL,
  [date_create] datetime  NULL,
  [is_del] int DEFAULT ((0)) NOT NULL,
  [remark] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  CONSTRAINT [sys_config_pk] PRIMARY KEY NONCLUSTERED ([id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
ON [PRIMARY]
)
ON [PRIMARY]
GO

ALTER TABLE [dbo].[sys_config] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'配置名称',
'SCHEMA', N'dbo',
'TABLE', N'sys_config',
'COLUMN', N'config_name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'备注',
'SCHEMA', N'dbo',
'TABLE', N'sys_config',
'COLUMN', N'remark'
GO

EXEC sp_addextendedproperty
'MS_Description', N'系统配置',
'SCHEMA', N'dbo',
'TABLE', N'sys_config'


INSERT INTO [dbo].[sys_user]([user_code], [user_name], [mobile], [password], [real_name], [work_phone], [email], [salt], [user_status], [dept_id], [date_create], [date_update], [is_del]) VALUES ( '00000', 'admin', '18888888888', '473d9b7e436095d7fb68663b81bb336a', '超级用户', '123456', 'dddd', NULL, 1, NULL, '2020-12-04 11:13:37.653', NULL, 0);

